package com.github.tommas.admintpl.bean.template;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

import java.util.*;

public class ReportTemplate {
    public static final String FILE_EXTENSION = ".xlsx";

    // size of 8 chars
    private static final int MIN_COLUMN_WIDTH = 8 * 256;

    private static final String HIDDEN_SHEET_NAME = "_template_info_";

    private static final int TEMPLATE_ID_ROW = 0;
    private static final int TEMPLATE_ID_COL = 1;
    private static final int TEMPLATE_VERSION_ROW = 1;
    private static final int TEMPLATE_VERSION_COL = 1;
    private static final int SHEET_NAME_ROW = 2;
    private static final int SHEET_NAME_COL = 0;
    private static final String SHEET_NAME_END_MARKER = "_sheet_name_ended_";
    private static final int MAX_SHEET_NAME_COUNT = 99;

    private final Integer templateId;
    private final String version;
    private TemplateDefinition definition;

    public ReportTemplate(Integer templateId, String version) {
        this(templateId, version, null);
    }

    public ReportTemplate(Integer templateId, String version, TemplateDefinition definition) {
        this.templateId = templateId;
        this.version = version;
        this.definition = definition;
    }

    public static ReportTemplate createFromWorkbook(Workbook workbook) {
        Sheet sheet = workbook.getSheet(HIDDEN_SHEET_NAME);
        if (sheet == null) {
            throw new RuntimeException("Invalid workbook. The workbook is not generated by ReportTemplate");
        }

        Row row = sheet.getRow(TEMPLATE_ID_ROW);
        Cell cell = row.getCell(TEMPLATE_ID_COL);
        Integer templateId = Integer.valueOf(cell.getStringCellValue());

        row = sheet.getRow(TEMPLATE_VERSION_ROW);
        cell = row.getCell(TEMPLATE_VERSION_COL);
        String version = cell.getStringCellValue();

        return new ReportTemplate(templateId, version);
    }

    public Integer getTemplateId() {
        return templateId;
    }

    public String getVersion() {
        return version;
    }

    public void setDefinition(TemplateDefinition definition) {
        this.definition = definition;
    }

    public Workbook generateWorkbook(String hiddenSheetPassword) {
        return generateWorkbook(hiddenSheetPassword, false);
    }

    public Workbook generateWorkbook(String hiddenSheetPassword, boolean keepIndicatorId) {
        Workbook workbook = generateWorkbook(keepIndicatorId);
        createReservedSheet(workbook, hiddenSheetPassword);
        return workbook;
    }

    private Workbook generateWorkbook(boolean keepIndicatorId) {
        Workbook workbook = new XSSFWorkbook();

        for (TemplateDefinition.Sheet sheetDef : definition.getSheets()) {
            Sheet sheet;
            if (StringUtils.hasLength(sheetDef.getName())) {
                sheet = workbook.createSheet(sheetDef.getName());
            } else {
                sheet = workbook.createSheet();
            }
            generateSheet(sheet, sheetDef.getDefinition(), keepIndicatorId);
        }

        return workbook;
    }

    private static void generateSheet(Sheet sheet, List<List<CellDefinition>> def, boolean keepIndicatorId) {
        Set<CellKey> mergedCells = new HashSet<>();
        int cellCount = 0;
        for (int rowIndex = 0; rowIndex < def.size(); rowIndex++) {
            Row excelRow = sheet.createRow(rowIndex);

            List<CellDefinition> cells = def.get(rowIndex);
            int cellIndex = 0;
            cellCount = 0;
            for (CellDefinition cell : cells) {
                int colspan = cell.getColSpan();
                int rowspan = cell.getRowSpan();

                colspan = colspan == 0 ? 1 : colspan;
                rowspan = rowspan == 0 ? 1 : rowspan;

                // skip merged cells
                while (mergedCells.contains(new CellKey(rowIndex, cellIndex))) {
                    cellIndex++;
                }

                Cell excelCell = excelRow.createCell(cellIndex);
                if (CellDefinition.STRING_TYPE.equals(cell.getType())) {
                    excelCell.setCellValue((String) cell.getValue());
                } else if (CellDefinition.INDICATOR_TYPE.equals(cell.getType()) && keepIndicatorId) {
                    // used for debug generated template
                    excelCell.setCellValue(resolveIndicatorId(cell));
                }

                if (cell.isHeader()) {
                    // TODO: set style
                }

                // merge cells
                if (colspan > 1 || rowspan > 1) {
                    CellRangeAddress range = new CellRangeAddress(
                            rowIndex, rowIndex + rowspan - 1,
                            cellIndex, cellIndex + colspan - 1
                    );
                    sheet.addMergedRegion(range);

                    for (int r = rowIndex; r < rowIndex + rowspan; r++) {
                        for (int c = cellIndex; c < cellIndex + colspan; c++) {
                            mergedCells.add(new CellKey(r, c));
                        }
                    }
                }

                cellIndex += colspan;
            }

            if (cellIndex > cellCount) {
                cellCount = cellIndex;
            }
        }

        // adjust column width
        for (int i = 0; i < cellCount; i++) {
            sheet.autoSizeColumn(i, true);

            int width = sheet.getColumnWidth(i);
            if (width < MIN_COLUMN_WIDTH) {
                sheet.setColumnWidth(i, MIN_COLUMN_WIDTH);
            }
        }
    }

    private void createReservedSheet(Workbook workbook, String hiddenSheetPassword) {
        Sheet sheet = workbook.createSheet(HIDDEN_SHEET_NAME);

        // template id
        Row row = sheet.createRow(TEMPLATE_ID_ROW);
        Cell cell = row.createCell(TEMPLATE_ID_COL - 1);
        cell.setCellValue("ID");
        cell = row.createCell(TEMPLATE_ID_COL);
        cell.setCellValue(String.valueOf(templateId));

        // template version
        row = sheet.createRow(TEMPLATE_VERSION_ROW);
        cell = row.createCell(TEMPLATE_VERSION_COL - 1);
        cell.setCellValue("VERSION");
        cell = row.createCell(TEMPLATE_VERSION_COL);
        cell.setCellValue(version);

        List<TemplateDefinition.Sheet> sheetDefs = definition.getSheets();

        // sheet names
        for (int i = 0, len = sheetDefs.size(); i < len; i++) {
            row = sheet.createRow(SHEET_NAME_ROW + i);
            String sheetName = workbook.getSheetAt(i).getSheetName();
            cell = row.createCell(SHEET_NAME_COL);
            cell.setCellValue(sheetName);

            // write name in definition when sheet name is modified
            if (!sheetName.equals(sheetDefs.get(i).getName())) {
                cell = row.createCell(SHEET_NAME_COL + 1);
                cell.setCellValue(sheetDefs.get(i).getName());
            }
        }
        row = sheet.createRow(SHEET_NAME_ROW + sheetDefs.size());
        cell = row.createCell(SHEET_NAME_COL);
        cell.setCellValue(SHEET_NAME_END_MARKER);

        int idx = workbook.getSheetIndex(sheet);
        workbook.setSheetHidden(idx, true);

        sheet.protectSheet(hiddenSheetPassword);
    }

    List<String> getSheetNamesFromWorkbook(Workbook workbook) {
        Assert.notNull(definition, "template definition must be set");

        Sheet sheet = workbook.getSheet(HIDDEN_SHEET_NAME);
        List<String> sheetNames = new ArrayList<>();
        for (int i = 0; i < MAX_SHEET_NAME_COUNT; i++) {
            Row row = sheet.getRow(SHEET_NAME_ROW+i);
            Cell cell = row.getCell(SHEET_NAME_COL);
            String sheetName = cell.getStringCellValue();
            if (SHEET_NAME_END_MARKER.equals(sheetName)) {
                break;
            }
            sheetNames.add(sheetName);
        }

        if (sheetNames.size() != definition.getSheets().size()) {
            throw new RuntimeException("template sheet info is not match with definition, try to regenerate a template");
        }

        return sheetNames;
    }

    public Iterator<IndicatorData> getIndicatorsFromWorkbook(Workbook workbook) {
        List<String> sheetNames = getSheetNamesFromWorkbook(workbook);
        return new IndicatorDataIterator(workbook, sheetNames);
    }

    private static List<IndicatorCell> getIndicators(List<List<CellDefinition>> definition) {
        List<IndicatorCell> indicators = new ArrayList<>();
        Set<CellKey> mergedCells = new HashSet<>();
        for (int rowIndex = 0; rowIndex < definition.size(); rowIndex++) {
            List<CellDefinition> cells = definition.get(rowIndex);
            int cellIndex = 0;
            for (CellDefinition cell : cells) {
                int colspan = cell.getColSpan();
                int rowspan = cell.getRowSpan();

                colspan = colspan == 0 ? 1 : colspan;
                rowspan = rowspan == 0 ? 1 : rowspan;

                // skip merged cells
                while (mergedCells.contains(new CellKey(rowIndex, cellIndex))) {
                    cellIndex++;
                }

                if (CellDefinition.INDICATOR_TYPE.equals(cell.getType())) {
                    indicators.add(new IndicatorCell(rowIndex, cellIndex, resolveIndicatorId(cell), cell.getCategoryId()));
                }

                // remember merge cells
                if (colspan > 1 || rowspan > 1) {
                    for (int r = rowIndex; r < rowIndex + rowspan; r++) {
                        for (int c = cellIndex; c < cellIndex + colspan; c++) {
                            mergedCells.add(new CellKey(r, c));
                        }
                    }
                }

                cellIndex += colspan;
            }
        }

        return indicators;
    }

    private static Integer resolveIndicatorId(CellDefinition cell) {
        Object v = cell.getValue();
        if (v instanceof String) {
            return Integer.valueOf((String) v);
        } else if (v instanceof Number) {
            return  ((Number) v).intValue();
        }

        throw new RuntimeException(String.format("Invalid indicator cell value: %s", v));
    }

    private static class CellKey {
        private final int row;
        private final int col;

        public CellKey(int row, int col) {
            this.row = row;
            this.col = col;
        }

        public int getRow() {
            return row;
        }

        public int getCol() {
            return col;
        }

        @Override
        public boolean equals(Object o) {
            if (this == o) return true;
            if (o == null || getClass() != o.getClass()) return false;
            CellKey rowCol = (CellKey) o;
            return row == rowCol.row && col == rowCol.col;
        }

        @Override
        public int hashCode() {
            return 31 * row + col;
        }
    }

    public static class IndicatorData {
        private final Integer definitionId;
        private final Integer categoryId;
        private final String value;

        public IndicatorData(Integer definitionId, Integer categoryId, String value) {
            this.definitionId = definitionId;
            this.categoryId = categoryId;
            this.value = value;
        }

        public Integer getDefinitionId() {
            return definitionId;
        }

        public Integer getCategoryId() {
            return categoryId;
        }

        public String getValue() {
            return value;
        }
    }

    private static class IndicatorCell {
        private final int row;
        private final int col;
        private final Integer indicatorId;
        private Integer categoryId;

        IndicatorCell(int row, int col, Integer indicatorId, Integer categoryId) {
            this.row = row;
            this.col = col;
            this.indicatorId = indicatorId;
        }
    }

    private class IndicatorDataIterator implements Iterator<IndicatorData> {
        final Workbook workbook;
        final List<String> sheetNames;
        int currentSheetIdx = 0;
        int currentCellIdx = 0;
        List<IndicatorCell> indicatorCells;

        IndicatorDataIterator(Workbook workbook, List<String> sheetNames) {
            this.workbook = workbook;
            this.sheetNames = sheetNames;
        }

        @Override
        public boolean hasNext() {
            return currentSheetIdx < sheetNames.size();
        }

        @Override
        public IndicatorData next() {
            String sheetName = sheetNames.get(currentSheetIdx);
            Sheet sheet = workbook.getSheet(sheetName);
            if (sheet == null) {
                throw new RuntimeException(String.format("Cannot find sheet: %s", sheetName));
            }

            if (indicatorCells == null) {
                TemplateDefinition.Sheet def = ReportTemplate.this.definition.getSheets().get(currentSheetIdx);
                indicatorCells = getIndicators(def.getDefinition());
            }

            IndicatorCell c = indicatorCells.get(currentCellIdx);
            Row row = sheet.getRow(c.row);
            Cell cell = row.getCell(c.col);

            String value = null;
            CellType ct = cell.getCellType();
            if (ct.equals(CellType.NUMERIC)) {
                value = String.valueOf(cell.getNumericCellValue());
            } else if (ct.equals(CellType.STRING)) {
                value = cell.getStringCellValue();
            }

            IndicatorData data = new IndicatorData(c.indicatorId, c.categoryId, value);

            // update status
            currentCellIdx++;

            // prepare for next sheet
            if (currentCellIdx == indicatorCells.size()) {
                indicatorCells = null;
                currentCellIdx = 0;
                currentSheetIdx++;
            }

            return data;
        }
    }
}
